SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_StatisticsRecruitmentUpdate]
	(
      @param_JOL_Statistics_StartTime VARCHAR(50),
      @param_JOL_Statistics_EndTime VARCHAR(50)
	)
AS
SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
DECLARE @Count int, @OnCampus int, @OnCampusUnit int, @OnCampusBooth int, @OnCampusRecruitmentNUM int, @OnNet int, @OnNetUnit int, @OnNetBooth int, @OnNetRecruitmentNUM int, @OnNetEmployNUM int, @TheOthers int

WHILE @param_JOL_Statistics_StartTime <= @param_JOL_Statistics_EndTime
BEGIN
	SET @OnCampus =(SELECT count(*)  
		FROM [dbo].[JOL_Recruitment]
		WHERE YEAR(start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND [type] = '校内专场'
			AND [state] = 1)
	SET @OnCampusUnit =(SELECT COUNT(DISTINCT b.employing_unit) 
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '校内专场'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @OnCampusBooth =(SELECT SUM(b.booth_quantity)  
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '校内专场'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @OnCampusRecruitmentNUM =(SELECT SUM(c.quantity)  
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
			INNER JOIN [dbo].[JOL_Recruitment_Position] AS c ON c.participants_apply = b.id
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '校内专场'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @OnNet =(SELECT count(*)  
		FROM [dbo].[JOL_Recruitment]
		WHERE YEAR(start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND [type] = '网络招聘会'
			AND [state] = 1)
	SET @OnNetUnit =(SELECT COUNT(DISTINCT b.employing_unit)
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '网络招聘会'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @OnNetBooth =(SELECT SUM(b.booth_quantity)  
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '网络招聘会'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @OnNetRecruitmentNUM =(SELECT SUM(c.quantity)  
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
			INNER JOIN [dbo].[JOL_Recruitment_Position] AS c ON c.participants_apply = b.id
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '网络招聘会'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @OnNetEmployNUM =(SELECT COUNT(*)
		FROM [dbo].[JOL_Recruitment] AS a INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
			INNER JOIN [dbo].[JOL_Recruitment_Position] AS c ON c.participants_apply = b.id
			INNER JOIN [dbo].[JOL_Recruitment_ApplyingRecords] AS d ON d.recruit_position = c.id
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[type] = '网络招聘会'
			AND a.[state] = 1 AND b.[state] = 1)
	SET @TheOthers =(SELECT count(*)  
		FROM [dbo].[JOL_Recruitment]
		WHERE YEAR(start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND [type] != '校内专场'
			AND [type] != '网络招聘会'
			AND [state] = 1)
			
	SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_Recruitment WHERE @param_JOL_Statistics_StartTime = Statistics_Date)
	IF (@Count = 0)
		BEGIN
			INSERT INTO [dbo].[JOL_Statistics_Recruitment]
			(
				Statistics_Date,
				Statistics_OnCampus,
				Statistics_OnCampusUnit,
				Statistics_OnCampusBooth,
				Statistics_OnCampusRecruitmentNUM,
				Statistics_OnNet,
				Statistics_OnNetUnit,
				Statistics_OnNetBooth,
				Statistics_OnNetRecruitmentNUM,
				Statistics_OnNetEmployNUM,
				Statistics_TheOthers,
				Statistics_AddTime,
				Statistics_UpdateTime
			)
			VALUES
			(
				@param_JOL_Statistics_StartTime,
				@OnCampus,
				@OnCampusUnit,
				@OnCampusBooth,
				@OnCampusRecruitmentNUM,
				@OnNet,
				@OnNetUnit,
				@OnNetBooth,
				@OnNetRecruitmentNUM,
				@OnNetEmployNUM,
				@TheOthers,
				GETDATE(),
				GETDATE()
			)
		END
	ELSE
		BEGIN
			UPDATE [dbo].[JOL_Statistics_Recruitment]
				SET Statistics_OnCampus = @OnCampus,
					Statistics_OnCampusUnit = @OnCampusUnit,
					Statistics_OnCampusBooth = @OnCampusBooth,
					Statistics_OnCampusRecruitmentNUM = @OnCampusRecruitmentNUM,
					Statistics_OnNet = @OnNet,
					Statistics_OnNetUnit = @OnNetUnit,
					Statistics_OnNetBooth = @OnNetBooth,
					Statistics_OnNetRecruitmentNUM = @OnNetRecruitmentNUM,
					Statistics_OnNetEmployNUM = @OnNetEmployNUM,
					Statistics_TheOthers = @TheOthers,
					Statistics_UpdateTime = GETDATE()
			WHERE Statistics_Date = @param_JOL_Statistics_StartTime
			
		END
	SET @param_JOL_Statistics_StartTime = CONVERT(VARCHAR(50), DATEADD(MONTH, 1, @param_JOL_Statistics_StartTime), 23)
END

GO
